package com.kingre.et.dao.jpa;

import java.util.List;

import com.kingre.et.dao.ITdataDAO;
import com.kingre.et.model.School;
import com.kingre.et.model.Tdata;
import com.kingre.et.model.User;

public class TdataDAOJPA extends GenericDAOJPA<Tdata, Integer> implements ITdataDAO {

    @SuppressWarnings("unchecked")
    public List<Tdata> findByUser(User user){
        return this.getJpaTemplate().find("FROM Tdata WHERE user = ? ORDER BY modelType", user);
    }
    @SuppressWarnings("unchecked")
    public List<Tdata> findStatByUser(User user, Integer type){
        return this.getJpaTemplate().find("FROM Tdata WHERE user = ? AND modelType = ? ORDER BY modelType", user, type);
    }
    @SuppressWarnings("unchecked")
    public Object[] findSchoolAVG(School school) {
    	String hql = "SELECT AVG(p.v1) as a1," +
            "AVG(p.v2) as a2," +
            "AVG(p.v3) as a3," +
            "AVG(p.v4) as a4," +
            "AVG(p.v5) as a5," +
            "AVG(p.v6) as a6," +
            "AVG(p.v7) as a7," +
            "AVG(p.v8) as a8," +
            "AVG(p.v9) as a9";
    	if(school.getSchoolType() == 20){
    		hql += " FROM Tdata p JOIN p.user t WHERE t.school = ? AND p.modelType = 123 ";
    	}
    	else
    		hql += " FROM Tdata p JOIN p.user t, School s WHERE s = ? and t.school MEMBER OF s.underSchool AND p.modelType = 123 ";
        List<Object[]> list = getJpaTemplate().find(hql, school);
        return list.get(0);
    }
    @SuppressWarnings("unchecked")
    public List<Tdata> findAllAVG(School school) {
    	String hql;
    	if(school.getSchoolType() == 20){
    		hql = "SELECT p  FROM Tdata p JOIN p.user t WHERE t.school = ? AND p.modelType = 123 ";
    	}
    	else
    		hql = "SELECT p  FROM Tdata p JOIN p.user t, School s WHERE s = ? and t.school MEMBER OF s.underSchool AND p.modelType = 123 ";
        List<Tdata> list = getJpaTemplate().find(hql, school);
        return list;
    }
    @SuppressWarnings("unchecked")
    public List<Tdata> findTdataBySchool(School school) {
    	if(school.getSchoolType() == 20){
    		return getJpaTemplate().find(
                "SELECT DISTINCT p FROM Tdata p JOIN p.user t WHERE t.school = ? AND p.modelType = 123 ", school);
    	}
    	else
    		return getJpaTemplate().find(
                    "SELECT DISTINCT p FROM Tdata p JOIN p.user t, School s WHERE s = ? and t.school MEMBER OF s.underSchool AND p.modelType = 123 ", school);
    }
}
